﻿using System.Data;
using DaoService.Model;
using Dapper;
using Microsoft.Data.SqlClient;
using MySql.Data.MySqlClient;
using UI.Utils;

namespace DaoService.Services;

public interface IPlcInfoService
{
    Task<int> InsertAsync(PlcInfoEntity model);
    Task<bool> UpdateAsync(PlcInfoEntity model);
    Task<bool> DeleteAsync(int id);
    Task<PlcInfoEntity> GetByIdAsync(int id);
    Task<IEnumerable<PlcInfoEntity>> GetAllAsync();
    Task<IEnumerable<PlcInfoEntity>> SearchAsync(string keyword);
    Task<int> SaveAsync(PlcInfoEntity model);
}
public class PlcInfoService : IPlcInfoService
{
    private readonly IDbConnection _dbConnection;

    public PlcInfoService(IDbConnection dbConnection)
    {
        _dbConnection = dbConnection;
    }

    public async Task<int> InsertAsync(PlcInfoEntity model)
    {
        const string sql = @"
            INSERT INTO plc_info (
                Id,
                OrderNum, 
                Name, 
                CreatedDate, 
                ModifiedDate, 
                CpuType, 
                IpAddress, 
                Rack, 
                Slot, 
                Timeout 
            ) 
            VALUES (
                @Id,
                @OrderNum, 
                @Name, 
                @CreatedDate, 
                @ModifiedDate, 
                @CpuType, 
                @IpAddress, 
                @Rack, 
                @Slot, 
                @Timeout
            );
            SELECT LAST_INSERT_ID();";

        model.CreatedDate = DateTime.Now;
        model.ModifiedDate = DateTime.Now;
        if (model.Id == 0)
        {
            model.Id = IntIdGenerator.IntUUID();
        }
        await _dbConnection.ExecuteScalarAsync<int>(sql, model);
        return model.Id;
    }

    public async Task<bool> UpdateAsync(PlcInfoEntity model)
    {
        const string sql = @"
            UPDATE plc_info 
            SET 
                OrderNum = @OrderNum,
                Name = @Name,
                ModifiedDate = @ModifiedDate,
                CpuType = @CpuType,
                IpAddress = @IpAddress,
                Rack = @Rack,
                Slot = @Slot,
                Timeout = @Timeout
            WHERE Id = @Id";

        model.ModifiedDate = DateTime.Now;

        var affectedRows = await _dbConnection.ExecuteAsync(sql, model);
        return affectedRows > 0;
    }

    public async Task<bool> DeleteAsync(int id)
    {
        const string sql = "DELETE FROM plc_info WHERE Id = @Id";
        var affectedRows = await _dbConnection.ExecuteAsync(sql, new { Id = id });
        return affectedRows > 0;
    }

    public async Task<PlcInfoEntity> GetByIdAsync(int id)
    {
        const string sql = "SELECT * FROM plc_info WHERE Id = @Id";
        return await _dbConnection.QueryFirstOrDefaultAsync<PlcInfoEntity>(sql, new { Id = id });
    }

    public async Task<IEnumerable<PlcInfoEntity>> GetAllAsync()
    {
        const string sql = "SELECT * FROM plc_info ORDER BY OrderNum ASC";
        return await _dbConnection.QueryAsync<PlcInfoEntity>(sql);
    }

    public async Task<IEnumerable<PlcInfoEntity>> SearchAsync(string keyword)
    {
        const string sql = @"
            SELECT * 
            FROM plc_info 
            WHERE 
                Name LIKE @Keyword OR 
                IpAddress LIKE @Keyword 
            ORDER BY OrderNum ASC";

        return await _dbConnection.QueryAsync<PlcInfoEntity>(
            sql, 
            new { Keyword = $"%{keyword}%" });
    }

    public async Task<int> SaveAsync(PlcInfoEntity model)
    {
        if (model.Id == 0)
        {
            return await InsertAsync(model);
        }
        else
        {
            var success = await UpdateAsync(model);
            return success ? model.Id : 0;
        }
    }
}